<!doctype html>
<html>
<head>
    <link rel="stylesheet" type="text/css" href="{{url_for('static',filename='style.css')}}">
   <style>
      a{display:inline-block;margin: 5px 0;border-radius: 4px;background: rgb(255,229,185);text-decoration: none;}
   </style>
</head>
   <body>
      <h1>analize.html</h1>
      <div id="tablesDiv">
      <table id="tabDiv1">
         <thead>
            <th>userid</th>
            <th>username</th>
            <th>sex</th>
            <th>sum</th>
            <th>count</th>
            <th>average</th>
         </thead>

         {% for row in rows %}
            <tr>
               <td>{{row["id"]}}</td>
               <td>{{row["username"]}}</td>
               <td> {{ row["sex"]}}</td>
               <td>{{row['sum']}}</td>
               <td>{{row['count']}}</td>
               <td>{{row['average']}}</td>
            </tr>
         {% endfor %}
      </table>
      </div>
      <button onclick="exp();">export to excel...</button>
   <script>
       function exp() {
     tablesToExcel(['tabDiv1',], ['成绩总排名'], "paiming.xls", "Excel");
 }
 //导出excel包含多个sheet
 //tables:tableId的数组;wsbames:sheet的名字数组;wbname:工作簿名字;appname:Excel
 function tablesToExcel(tables, wsnames, wbname, appname) {

     var uri = 'data:application/vnd.ms-excel;base64,',
         tmplWorkbookXML = '<?xml version="1.0"?><?mso-application progid="Excel.Sheet"?><Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">' +
         '<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office"><Author>Axel Richter</Author><Created>{created}</Created></DocumentProperties>' +
         's>' +
         '<Style ss:ID="Currency"><NumberFormat ss:Format="Currency"></NumberFormat></Style>' +
         '<Style ss:ID="Date"><NumberFormat ss:Format="Medium Date"></NumberFormat></Style>' +
         '</Styles>' +
         '{worksheets}</Workbook>',
         tmplWorksheetXML = '<Worksheet ss:Name="{nameWS}"><Table>{rows}</Table></Worksheet>',
         tmplCellXML = '<Cell{attributeStyleID}{attributeFormula}><Data ss:Type="{nameType}">{data}</Data></Cell>',
         base64 = function(s) {
             return window.btoa(unescape(encodeURIComponent(s)))
         },
         format = function(s, c) {
             return s.replace(/{(\w+)}/g, function(m, p) {
                 return c[p];
             })
         }

     var ctx = "";
     var workbookXML = "";
     var worksheetsXML = "";
     var rowsXML = "";

     for (var i = 0; i < tables.length; i++) {
         if (!tables[i].nodeType)
             tables[i] = document.getElementById(tables[i]);

         //           控制要导出的行数
         for (var j = 0; j < tables[i].rows.length; j++) {
             rowsXML += '<Row>';

             for (var k = 0; k < tables[i].rows[j].cells.length; k++) {
                 var dataType = tables[i].rows[j].cells[k].getAttribute("data-type");
                 var dataStyle = tables[i].rows[j].cells[k].getAttribute("data-style");
                 var dataValue = tables[i].rows[j].cells[k].getAttribute("data-value");
                 dataValue = (dataValue) ? dataValue : tables[i].rows[j].cells[k].innerHTML;
                 var dataFormula = tables[i].rows[j].cells[k].getAttribute("data-formula");
                 dataFormula = (dataFormula) ? dataFormula : (appname == 'Calc' && dataType == 'DateTime') ? dataValue : null;
                 ctx = {
                     attributeStyleID: (dataStyle == 'Currency' || dataStyle == 'Date') ? ' ss:StyleID="' + dataStyle + '"' : '',
                     nameType: (dataType == 'Number' || dataType == 'DateTime' || dataType == 'Boolean' || dataType == 'Error') ? dataType : 'String',
                     data: (dataFormula) ? '' : dataValue,
                     attributeFormula: (dataFormula) ? ' ss:Formula="' + dataFormula + '"' : ''
                 };
                 rowsXML += format(tmplCellXML, ctx);
             }
             rowsXML += '</Row>'
         }
         ctx = {
             rows: rowsXML,
             nameWS: wsnames[i] || 'Sheet' + i
         };
         worksheetsXML += format(tmplWorksheetXML, ctx);
         rowsXML = "";
     }

     ctx = {
         created: (new Date()).getTime(),
         worksheets: worksheetsXML
     };
     workbookXML = format(tmplWorkbookXML, ctx);

     //       查看后台的打印输出
     //console.log(workbookXML);

     var link = document.createElement("A");
     link.href = uri + base64(workbookXML);
     link.download = wbname || 'Workbook.xls';
     link.target = '_blank';
     document.body.appendChild(link);
     link.click();
     document.body.removeChild(link);
 }
   </script>
   </body>
</html>